과제

  • 다방 => 댜뱡
  • 40개의 매물 데이터 => provider, agency, room
  • provider, agency, room 테이블에 데이터 추가하기 + JOIN 한번씩만 해보기

  1. 데이터베이스 생성하기 - CREATE DATABASE
  2. 테이블 생성하기 ( * ) - CREATE TABLE
  3. 데이터 추가하기 - INSERT INTO

In [1]:
import pymysql

In [2]:
db = pymysql.connect(
    "db.fastcamp.us",
    "root",
    "dkstncks",
    "kipoy",
    charset="utf8"
)

In [3]:
cursor = db.cursor()

In [4]:
SQL_QUERY = """
    SHOW TABLES;
"""
pd.read_sql(SQL_QUERY, db)


Out[4]:
Tables_in_kipoy
0 agency
1 provider

In [5]:
zig_df = pd.read_csv("zigbang.csv")

In [9]:
zig_df.head(2)


Out[9]:
직방 매물번호 연락처 이메일 부동산 보증금 월세
0 3467204.0 010-4780-3740 서울특별시 강남구 논현1동 124-33 TODAY공인중개사(이인규) 1000.0 100.0
1 3150497.0 010-4780-3740 서울특별시 강남구 논현1동 124-33 TODAY공인중개사(이인규) 11000.0 60.0

In [12]:
SQL_QUERY = """
    INSERT INTO room (address, deposit, rent)
    ;
"""
cursor.execute(SQL_QUERY)
db.commit()


---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
<ipython-input-12-80bed5146485> in <module>()
      3     ;
      4 """
----> 5 cursor.execute(SQL_QUERY)
      6 db.commit()

C:\Anaconda3\lib\site-packages\pymysql\cursors.py in execute(self, query, args)
    164         query = self.mogrify(query, args)
    165 
--> 166         result = self._query(query)
    167         self._executed = query
    168         return result

C:\Anaconda3\lib\site-packages\pymysql\cursors.py in _query(self, q)
    320         conn = self._get_db()
    321         self._last_executed = q
--> 322         conn.query(q)
    323         self._do_get_result()
    324         return self.rowcount

C:\Anaconda3\lib\site-packages\pymysql\connections.py in query(self, sql, unbuffered)
    835                 sql = sql.encode(self.encoding, 'surrogateescape')
    836         self._execute_command(COMMAND.COM_QUERY, sql)
--> 837         self._affected_rows = self._read_query_result(unbuffered=unbuffered)
    838         return self._affected_rows
    839 

C:\Anaconda3\lib\site-packages\pymysql\connections.py in _read_query_result(self, unbuffered)
   1019         else:
   1020             result = MySQLResult(self)
-> 1021             result.read()
   1022         self._result = result
   1023         if result.server_status is not None:

C:\Anaconda3\lib\site-packages\pymysql\connections.py in read(self)
   1302     def read(self):
   1303         try:
-> 1304             first_packet = self.connection._read_packet()
   1305 
   1306             if first_packet.is_ok_packet():

C:\Anaconda3\lib\site-packages\pymysql\connections.py in _read_packet(self, packet_type)
    981 
    982         packet = packet_type(buff, self.encoding)
--> 983         packet.check_error()
    984         return packet
    985 

C:\Anaconda3\lib\site-packages\pymysql\connections.py in check_error(self)
    393             errno = self.read_uint16()
    394             if DEBUG: print("errno =", errno)
--> 395             err.raise_mysql_exception(self._data)
    396 
    397     def dump(self):

C:\Anaconda3\lib\site-packages\pymysql\err.py in raise_mysql_exception(data)
    100     errval = data[4:].decode('utf-8', 'replace')
    101     errorclass = error_map.get(errno, InternalError)
--> 102     raise errorclass(errno, errval)

ProgrammingError: (1064, "42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1")

In [ ]: